package dao;

import util.DBUtil;
import java.sql.*;
import java.util.Vector;

public class AttendanceDAO {
    public boolean signIn(String studentName, String courseName, Date date) {

        try (Connection conn = DBUtil.getConnection()) {
            // 学生
            int studentId = getOrInsert(conn, "students", "name", studentName);
            int courseId = getOrInsert(conn, "courses", "course_name", courseName);

            String sql = "INSERT INTO attendance(student_id, course_id, date) VALUES (?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setInt(1, studentId);
                stmt.setInt(2, courseId);
                stmt.setDate(3, date);
                stmt.executeUpdate();
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    private int getOrInsert(Connection conn, String table, String column, String value) throws SQLException {
        String selectSql = "SELECT id FROM " + table + " WHERE " + column + "=?";
        try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
            stmt.setString(1, value);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) return rs.getInt("id");
        }

        String insertSql = "INSERT INTO " + table + "(" + column + ") VALUES (?)";
        try (PreparedStatement stmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, value);
            stmt.executeUpdate();
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) return rs.getInt(1);
        }
        return -1;
    }

    public Vector<Vector<Object>> getAllRecords() {
        Vector<Vector<Object>> data = new Vector<>();
        String sql = "SELECT s.name, c.course_name, a.date FROM attendance a " +
                "JOIN students s ON a.student_id = s.id " +
                "JOIN courses c ON a.course_id = c.id ORDER BY a.date DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                Vector<Object> row = new Vector<>();
                row.add(rs.getString("name"));
                row.add(rs.getString("course_name"));
                row.add(rs.getDate("date"));
                data.add(row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return data;
    }
}
